﻿/***********************************************************************************************************************************
 *    Name: 
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE master

go

if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END

go

DECLARE @AccountName nvarchar(260)
SET @AccountName = SUSER_SNAME(0x010500000000000515000000dd840beb6592c5ebf7d37de0504c300c)

if not exists (select name from syslogins where name = @AccountName and hasaccess = 1 and isntname = 1)
BEGIN
EXEC sp_grantlogin @AccountName
END

go
--==================================================================================================================================

USE [ReportServer]

go

if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END

go

DECLARE @AccountName nvarchar(260)
SET @AccountName = SUSER_SNAME(0x010500000000000515000000dd840beb6592c5ebf7d37de0504c300c)

DECLARE @name_in_db nvarchar(260)
select @name_in_db = sysusers.name from sysusers inner join master.dbo.syslogins logins on logins.sid = sysusers.sid where logins.name = @AccountName and logins.isntname = 1
if @name_in_db IS NULL
BEGIN
EXEC sp_grantdbaccess @AccountName, @name_in_db OUTPUT
END
IF @name_in_db IS NOT NULL AND @name_in_db != 'dbo' AND @name_in_db != 'sys'
BEGIN
EXEC sp_addrolemember 'RSExecRole', @name_in_db
END

go
--==================================================================================================================================

USE [ReportServerTempDB]

go

if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END

go

DECLARE @AccountName nvarchar(260)
SET @AccountName = SUSER_SNAME(0x010500000000000515000000dd840beb6592c5ebf7d37de0504c300c)

DECLARE @name_in_db nvarchar(260)
select @name_in_db = sysusers.name from sysusers inner join master.dbo.syslogins logins on logins.sid = sysusers.sid where logins.name = @AccountName and logins.isntname = 1
if @name_in_db IS NULL
BEGIN
EXEC sp_grantdbaccess @AccountName, @name_in_db OUTPUT
END
IF @name_in_db IS NOT NULL AND @name_in_db != 'dbo' AND @name_in_db != 'sys'
BEGIN
EXEC sp_addrolemember 'RSExecRole', @name_in_db
END

go
--==================================================================================================================================

USE msdb

go

if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
 EXEC sp_addrole 'RSExecRole'
END

go

DECLARE @AccountName nvarchar(260)
SET @AccountName = SUSER_SNAME(0x010500000000000515000000dd840beb6592c5ebf7d37de0504c300c)

DECLARE @name_in_db nvarchar(260)
select @name_in_db = sysusers.name from sysusers inner join master.dbo.syslogins logins on logins.sid = sysusers.sid where logins.name = @AccountName and logins.isntname = 1
if @name_in_db IS NULL
BEGIN
EXEC sp_grantdbaccess @AccountName, @name_in_db OUTPUT
END
IF @name_in_db IS NOT NULL AND @name_in_db != 'dbo' AND @name_in_db != 'sys'
BEGIN
EXEC sp_addrolemember 'RSExecRole', @name_in_db
END

go
--==================================================================================================================================

USE master

go

DECLARE @AccountName nvarchar(260)
SET @AccountName = SUSER_SNAME(0x010500000000000515000000dd840beb6592c5ebf7d37de0504c300c)

DECLARE @name_in_db nvarchar(260)
select @name_in_db = sysusers.name from sysusers inner join master.dbo.syslogins logins on logins.sid = sysusers.sid where logins.name = @AccountName and logins.isntname = 1
if @name_in_db IS NULL
BEGIN
EXEC sp_grantdbaccess @AccountName, @name_in_db OUTPUT
END
IF @name_in_db IS NOT NULL AND @name_in_db != 'dbo' AND @name_in_db != 'sys'
BEGIN
EXEC sp_addrolemember 'RSExecRole', @name_in_db
END

go
--==================================================================================================================================

USE [ReportServer]
if exists (select 1 from [dbo].[sysobjects] where id = object_id(N'[dbo].[schedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	declare id_cursor cursor for select scheduleId from schedule s join msdb.dbo.sysjobs j  on (convert(sysname, s.scheduleid) = j.name) open id_cursor
	declare @next_id uniqueidentifier, @account nvarchar(260)
	select @account = SUSER_SNAME(0x010500000000000515000000dd840beb6592c5ebf7d37de0504c300c)
	fetch NEXT from id_cursor INTO @next_id
	while (@@fetch_status <> -1)
	BEGIN
		if (@@fetch_status <> -2)
		BEGIN
			exec msdb.dbo.sp_update_job @job_name = @next_id, @owner_login_name=@account
		END
	fetch NEXT FROM id_cursor INTO @next_id
	END
	close id_cursor
	deallocate id_cursor
END
